*All Census files are from main files in 2012 folders.

* Bring in Census of Services
use ${data}/csr2012base.dta, clear
drop if RESPONSE_TYPE=="NONML"
rename *, lower
cap rename state state_fips
keep if sales>0
egen tag_id = tag(survu_id)
keep if tag_id
drop tag_id
keep lbdnum_c201500 survu_id county_fips state_fips  msa  sales  
tempfile temp1
save `temp1'

* Bring in Census of Retail
use ${data}/crt2012base.dta, clear
drop if RESPONSE_TYPE=="NONML"
rename *, lower
egen tag_id = tag(survu_id)
keep if sales>0
keep if tag_id
drop tag_id
keep lbdnum_c201500 survu_id county_fips state_fips  msa  sales  
append using `temp1'
save `temp1', replace

*Bring in Census of Wholesalers
use ${data}/cwh2012base.dta, clear
drop if RESPONSE_TYPE=="NONML"
rename *, lower
egen tag_id = tag(survu_id)
keep if sales>0
keep if tag_id
drop tag_id
keep lbdnum_c201500 survu_id county_fips state_fips  msa  sales  
append using `temp1'
save `temp1', replace

*Bring in Census of Finance and Insurance
use ${data}/cfi2012base.dta, clear
drop if RESPONSE_TYPE=="NONML"
rename *, lower
egen tag_id = tag(survu_id)
keep if sales>0
keep if tag_id
drop tag_id
keep lbdnum_c201500 survu_id county_fips state_fips  msa  sales  
append using `temp1'
save `temp1', replace

*Bring in Census of Manufactures
use ${data}/cmf2012.dta, clear
drop if RESPONSE_TYPE=="NONML"
rename *, lower
egen tag_id = tag(survu_id)
rename cou county_fips
rename fipsst state_fips
rename tvs sales
keep if sales>0
keep if tag_id
drop tag_id
keep lbdnum_c201500 survu_id county_fips state_fips  msa  sales  
append using `temp1'
save `temp1', replace

*Bring in Census of Mining
use ${data}/cmi2012_final.dta, clear
keep if MAILFLG=="1" | MAILFLG=="6" | MAILFLG=="7" | MAILFLG=="S" | MAILFLG=="F" | MAILFLG=="T"
rename *, lower
rename ecrcptot sales
rename stfips state_fips
rename ctyfips county_fips
tostring survu_id, replace
keep if sales>0
egen tag_id = tag(survu_id)
keep if tag_id
drop tag_id
keep lbdnum_c201500 survu_id county_fips state  msa  sales  
append using `temp1'
save `temp1', replace

*Bring in Census of Utilities
use ${data}/cut2012base.dta, clear
drop if RESPONSE_TYPE=="NONML"
rename *, lower
keep if sales>0
egen tag_id = tag(survu_id)
keep if tag_id
drop tag_id
keep lbdnum_c201500 survu_id county_fips state  msa  sales  
append using `temp1'
save `temp1', replace

gen year=2012

*Drop if no lbd, because we cannot determine plant age.
drop if lbdnum == "" 
ren (year lbdnum_c201500) (T lbdnum)

save `temp1', replace


*Keep only tradables from lbd
use ${data}/lbd_c15_12.dta, replace
tempvar temp2
gen `temp2'=substr(bestnaics,1,4)
destring `temp2', gen(naics4)
*We use the 4-digit NAICS data set provided by Mian and Sufi (2014)
merge m:1 naics4 using ${data}/tradableindustries
keep if _m==3
drop _m
drop naics4
merge 1:m lbdnum using `temp1'
keep if _m==3
drop _m

gen fipsstco =  state + county if state!="" & county!=""
replace fipsstco = fipsst + county if state=="" | county==""

destring fipsstco, force replace
drop if fipsstco==.
ren msa msa_lbd

*This crosswalk is the same as list1.xls
cap drop _m
merge m:1 fipsstco using ${data}/county_to_cbsa_crosswalk.dta
drop if _m!=3

gen age = 2012-firstyear
gen die_before_2015 = lastyear <2015
ren naics naics_cmf
keep lbdnum T bestnaics naics_cmf fipsstco cbsa msa_lbd  emp_lbd   age die sales firmid

gen lspw = log(sales/emp_lbd)
drop if lspw==.

save ${data}/tradables_lbd_cbsa.dta, replace


tempvar temp1
gen `temp1'=substr(bestnaics,1,4)
destring `temp1', gen(naics4)
keep if naics4>4999
drop naics4
drop __*
save ${data}/tradableservices_lbd_cbsa.dta, replace
